package com.ruoyi.system.mapper;

import com.ruoyi.common.annotation.Log;
import com.ruoyi.system.domain.SyseEventsPosted;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * 赛事页面 数据层
 *
 * @author ganshiyu
 */
public interface SysEventsPostedMapper {

    /**
     * 查询活动结束时间
     */
    @Select("select epfinalend from events_posted where epid=#{epid}")
    public String queryEndTimeByEpid(String epid);


    /**
     * 教师页面我的邀请查询被邀请的赛事信息
     * @param tid
     * @return
     */
    @Select("select ep.*,suj.jid,suj.`status` from sys_user_judges suj,events_posted ep where suj.epid=ep.epid and epstate='1' and  suj.uid=#{tid}")
    public List<SyseEventsPosted> queryEventByTid(String tid);

    /**
     * 查询所有赛事信息
     */
    public List<SyseEventsPosted> queryAllSyseEventsPosted(SyseEventsPosted syseEventsPosted);

    /**
     * 教师页面查询所有区域级赛事信息
     */
    @Select("SELECT * FROM `kabadb`.`events_posted` where eplevel='学校级'")
    public List<SyseEventsPosted> querySyseEventsPostedByEplevel();


    /**
     =======
     /**
     * 添加赛事发布
     * 后台管理中的大事管理和校园工作台共同使用接口
     * @param syseEventsPosted 实体类
     * @return 结果
     */
    @Insert("<script>"+
            "insert into events_posted(eptitle,epintro,epsponsor" +

            " <if test='eporganizer!=\"\" and eporganizer!=null'>"+
            ",eporganizer"+
            "</if>"+

            " <if test='eppro!=\"\" and eppro!=null'>"+
            ",eppro"+
            "</if>"+

            " <if test='epcity!=\"\" and epcity!=null'>"+
            ",epcity"+
            "</if>"+

            " <if test='eparea!=\"\" and eparea!=null'>"+
            ",eparea"+
            "</if>"+

            ",epsigstart,epsigend" +

            " <if test='epprestart!=\"\" and epprestart!=null'>"+
            ",epprestart"+
            "</if>"+

            " <if test='eppreend!=\"\" and eppreend!=null'>"+
            ",eppreend"+
            "</if>"+

            " <if test='epsecstart!=\"\" and epsecstart!=null'>"+
            ",epsecstart"+
            "</if>"+

            " <if test='epsecend!=\"\" and epsecend!=null'>"+
            ",epsecend"+
            "</if>"+

            ",epfinalstart,epfinalend,epcharge,epchargetel,epstate,eplevel,epexplan,eprules,epadwars"+

            " <if test='eppic!=\"\" and eppic!=null'>"+
            ",eppic"+
            "</if>"+

            " <if test='epschool!=\"\" and epschool!=null'>"+
            ",epschool"+
            "</if>"+

            " <if test='eporganizerid!=\"\" and eporganizerid!=null'>"+
            ",eporganizerid"+
            "</if>"+

            " <if test='eventgroup!=\"\" and eventgroup!=null'>"+
            ",eventgroup"+
            "</if>"+
            " <if test='eppreexpired!=\"\" and eppreexpired!=null'>"+
            ",eppreexpired"+
            "</if>"+
            " <if test='epsecexpired!=\"\" and epsecexpired!=null'>"+
            ",epsecexpired"+
            "</if>"+
            " <if test='epfinalexpired!=\"\" and epfinalexpired!=null'>"+
            ",epfinalexpired"+
            "</if>"+
            " <if test='needApprove!=\"\" and needApprove!=null'>"+
            " , needApprove"+
            "</if>"+
            " <if test='progressStatus!=\"\" and progressStatus!=null'>"+
            " , progressStatus"+
            "</if>"+
            ",epstage,epworks,eptid,epcreatetime,epsponsorid) values(#{eptitle},#{epintro},#{epsponsor}" +

            " <if test='eporganizer!=\"\" and eporganizer!=null'>"+
            ",#{eporganizer}"+
            "</if>"+

            " <if test='eppro!=\"\" and eppro!=null'>"+
            ",#{eppro}"+
            "</if>"+

            " <if test='epcity!=\"\" and epcity!=null'>"+
            ",#{epcity}"+
            "</if>"+

            " <if test='eparea!=\"\" and eparea!=null'>"+
            ",#{eparea}"+
            "</if>"+

            ",#{epsigstart},#{epsigend}" +

            " <if test='epprestart!=\"\" and epprestart!=null'>"+
            ",#{epprestart}"+
            "</if>"+

            " <if test='eppreend!=\"\" and eppreend!=null'>"+
            ",#{eppreend}"+
            "</if>"+

            " <if test='epsecstart!=\"\" and epsecstart!=null'>"+
            ",#{epsecstart}"+
            "</if>"+


            " <if test='epsecend!=\"\" and epsecend!=null'>"+
            ",#{epsecend}"+
            "</if>"+

            ",#{epfinalstart},#{epfinalend},#{epcharge},#{epchargetel}," +
            "#{epstate},#{eplevel},#{epexplan},#{eprules},#{epadwars}" +

            " <if test='eppic!=\"\" and eppic!=null'>"+
            ",#{eppic}"+
            "</if>"+

            " <if test='epschool!=\"\" and epschool!=null'>"+
            ",#{epschool}"+
            "</if>"+
            " <if test='eporganizerid!=\"\" and eporganizerid!=null'>"+
            ",#{eporganizerid}"+
            "</if>"+
            " <if test='eventgroup!=\"\" and eventgroup!=null'>"+
            ",#{eventgroup}"+
            "</if>"+
            " <if test='eppreexpired!=\"\" and eppreexpired!=null'>"+
            ",#{eppreexpired}"+
            "</if>"+
            " <if test='epsecexpired!=\"\" and epsecexpired!=null'>"+
            ",#{epsecexpired}"+
            "</if>"+
            " <if test='epfinalexpired!=\"\" and epfinalexpired!=null'>"+
            ",#{epfinalexpired}"+
            "</if>"+
            " <if test='needApprove!=\"\" and needApprove!=null'>"+
            " , #{needApprove}"+
            "</if>"+
            " <if test='progressStatus!=\"\" and progressStatus!=null'>"+
            " , #{progressStatus} "+
            "</if>"+
            ",#{epstage},#{epworks},#{eptid},#{epcreatetime},#{epsponsorid})"+
            "</script>"
    )
    int insertEventsPosted(SyseEventsPosted syseEventsPosted);

    /**
     *  赛事管理  条件查询+分页
     *  后台管理中的大事管理和校园工作台共同使用接口
     *  当epschool不为空时是校园工作台在使用接口
     *  当epschool为空是是后台管理在使用接口
     * @param syseEventsPosted 实体类
     * @return 对象列表
     */
    @Select("<script> "+
            "select epid,eptitle,epcharge,epsponsor,eporganizer,eplevel,epsigstart,epfinalend,eppic,(case  " +
            " when epstate='0' then '未发布' "+
            "        when epstate='1' then'已发布'" +
            "        when epstate='2' then'已结束' " +
            "   end)'epstate', " +
            " eppreexpired, epsecexpired, epfinalexpired, " +
            " eventgroup, " +
            " needApprove, " +
            " (case when progressStatus='2' then'已结束' " +
            "        when progressStatus='3' then'报名中' " +
            "        when progressStatus='4' then'初赛中' " +
            "        when progressStatus='5' then'复赛中' " +
            "        when progressStatus='6' then'决赛中  ' " +
            "   end)'progressStatus' " +
            "  from events_posted where 1=1"+
            " <if test='eptitle!=\"\" and eptitle!=null'>"+
            "and eptitle=#{eptitle}"+
            "</if>"+
            " <if test='epsponsor!=\"\" and epsponsor!=null'>"+
            "and epsponsor=#{epsponsor}"+
            "</if>"+
            " <if test='epstate!=\"\" and epstate!=null'>"+
            "and epstate in(case  when epstate=#{epstate} then #{epstate} end)"+
            "</if>"+
            " <if test='eplevel!=\"\" and eplevel!=null'>"+
            "and eplevel=#{eplevel}"+
            "</if>"+
            " <if test='epschool!=\"\" and epschool!=null'>"+
            " and epschool=#{epschool}"+
            "</if>"+
            " <if test='eventgroup!=\"\" and eventgroup!=null'>"+
            " and eventgroup=#{eventgroup}"+
            "</if>"+
            " <if test='progressStatus!=\"\" and progressStatus!=null'>"+
            " and progressStatus=#{progressStatus}"+
            "</if>"+
            " order by epstate asc, epid desc "+
            "LIMIT #{currentPage},#{pageSize} "+
            "</script>")
    List<SyseEventsPosted> selectByPage(SyseEventsPosted syseEventsPosted);

    /*
     * 查询赛事表总数据量
     * 后台管理中的大事管理和校园工作台共同使用接口
     * 当epschool不为空时是校园工作台在使用接口
     * 当epschool为空是是后台管理在使用接口
     * */
    @Select("<script> " +
            "select count(1) from events_posted  where 1=1"+
            " <if test='eptitle!=\"\" and eptitle!=null'>"+
            "and eptitle=#{eptitle}"+
            "</if>"+
            " <if test='epsponsor!=\"\" and epsponsor!=null'>"+
            "and epsponsor=#{epsponsor}"+
            "</if>"+
            " <if test='epstate!=\"\" and epstate!=null'>"+
            "and epstate in(case  when epstate=#{epstate} then #{epstate} end)"+
            "</if>"+
            " <if test='eplevel!=\"\" and eplevel!=null'>"+
            "and eplevel=#{eplevel}"+
            "</if>"+
            " <if test='epschool!=\"\" and epschool!=null'>"+
            "and epschool=#{epschool}"+
            "</if>"+
            " <if test='eventgroup!=\"\" and eventgroup!=null'>"+
            " and eventgroup=#{eventgroup}"+
            "</if>"+
            " <if test='progressStatus!=\"\" and progressStatus!=null'>"+
            " and progressStatus=#{progressStatus}"+
            "</if>"+
            "</script>")
    long selectCount(SyseEventsPosted syseEventsPosted);


    /**根据赛事状态进行查询*/
    @Select("<script>select * from events_posted where epstate = #{epState}" +
            " <if test='epschool !=\"\" and epschool !=null'>"+
            "   and epschool=#{epschool}"+
            " </if>"+
            "</script>")
    List<SyseEventsPosted> selectEpTitle(@Param("epState") String epState,@Param("epschool") String epschool);

    /**
     * 修改赛事
     * @param syseEventsPosted 实体类
     *  后台管理中的大事管理和校园工作台共同使用接口
     * @return 结果
     */
    @Update("<script>"+
            "update events_posted set eptitle=#{eptitle},epintro=#{epintro},epsponsor=#{epsponsor},eporganizer=#{eporganizer}" +
            " <if test='eppro!=\"\" and eppro!=null'>"+
            ",eppro=#{eppro}"+
            "</if>"+
            " <if test='epcity!=\"\" and epcity!=null'>"+
            ",epcity=#{epcity}"+
            "</if>"+

            " <if test='eparea!=\"\" and eparea!=null'>"+
            ",eparea=#{eparea}"+
            "</if>"+

            ",epsigstart=#{epsigstart},epsigend=#{epsigend}" +

            " <if test='epprestart!=\"\" and epprestart!=null'>"+
            ",epprestart=#{epprestart}"+
            "</if>"+

            " <if test='eppreend!=\"\" and eppreend!=null'>"+
            ",eppreend=#{eppreend}"+
            "</if>"+

            " <if test='epsecstart!=\"\" and epsecstart!=null'>"+
            ",epsecstart=#{epsecstart}"+
            "</if>"+

            " <if test='epsecend!=\"\" and epsecend!=null'>"+
            ",epsecend=#{epsecend}"+
            "</if>"+
            ",epfinalstart=#{epfinalstart},epfinalend=#{epfinalend},epcharge=#{epcharge},epchargetel=#{epchargetel},"+
            "epstate=#{epstate},eplevel=#{eplevel},epexplan=#{epexplan},eprules=#{eprules},epadwars=#{epadwars}" +

            " <if test='eppic!=\"\" and eppic!=null'>"+
            ",eppic=#{eppic}"+
            "</if>"+

            " <if test='eporganizerid!=\"\" and eporganizerid!=null'>"+
            ",eporganizerid=#{eporganizerid}"+
            "</if>"+

            " <if test='eventgroup!=\"\" and eventgroup!=null'>"+
            ",eventgroup=#{eventgroup}"+
            "</if>"+
            " <if test='eppreexpired!=\"\" and eppreexpired!=null'>"+
            ",eppreexpired=#{eppreexpired}"+
            "</if>"+
            " <if test='epsecexpired!=\"\" and epsecexpired!=null'>"+
            ",epsecexpired=#{epsecexpired}"+
            "</if>"+
            " <if test='epfinalexpired!=\"\" and epfinalexpired!=null'>"+
            ",epfinalexpired=#{epfinalexpired}"+
            "</if>"+
            " <if test='progressStatus!=\"\" and progressStatus!=null'>"+
            " , progressStatus=#{progressStatus}"+
            "</if>"+
            " <if test='needApprove!=\"\" and needApprove!=null'>"+
            " , needApprove=#{needApprove}"+
            "</if>"+
            ",epstage=#{epstage},epworks=#{epworks},epschool=#{epschool},eptid=#{eptid},epedittime=#{epedittime}" +
            " <if test='epsponsorid!=\"\" and epsponsorid!=null'>"+
            " ,epsponsorid=#{epsponsorid}"+
            "</if>"+
            " where epid=#{epid}"+
            "</script>"
    )
    int edit(SyseEventsPosted syseEventsPosted);

    /**
     *  删除赛事
     *  后台管理中的大事管理和校园工作台共同使用接口
     * @param epid
     * @return 结果
     */
    @Delete("delete from events_posted where epid=#{epid}")
    int remove(int epid);
    /**
     *修改获奖名单路径
     */
    @Update("update events_posted set rankingpath=#{rankingpath} where epid=#{epid}")
    void updateRankingpath(SyseEventsPosted syseEventsPosted);
    /**
     *  发布
     *  后台管理中的大事管理和校园工作台共同使用接口
     * @param epid
     * @return 结果
     */
    @Update("update events_posted set epstate=1 where epid=#{epid}")
    int editState(int epid);

    /**
     *  通过id查询对象
     *  后台管理中的大事管理和校园工作台共同使用接口
     * @param epid
     * @return 对象
     */
    @Select("select * from events_posted where epid = #{epid}")
    SyseEventsPosted selectById(int epid);

    /**
     *  通过id和状态查询对象
     * @param epid
     * @return 对象
     */
    @Select("select * from events_posted where epid = #{epid} and epstate = 1")
    SyseEventsPosted selectById2(int epid);

    /*
     * 查询标题
     * */
    @Select("select distinct  eptitle from events_posted")
    List<SyseEventsPosted> selectTitle();

    /*
     * 查询主办方
     * */
    @Select("select distinct  epsponsor from events_posted")
    List<SyseEventsPosted> selectSponsor();

    /*
     * 查询赛事状态
     * */
    @Select("select distinct  epstate from events_posted")
    List<SyseEventsPosted> selectEpstate();

    /*
     * 查询赛事级别
     * */
    @Select("select distinct  eplevel from events_posted")
    List<SyseEventsPosted> selectEplevel();


    /**
     * 根据 学校名称和 赛事级别查询
     * */
    @Select("select epid,eptitle,epintro,eppic,epsponsor,eporganizer,eppro,epcity,eparea,epsigstart,epsigend,DATE_FORMAT(epprestart,'%Y年%m月%d') as epprestart,eppreend,epsecstart,epsecend,epfinalstart,DATE_FORMAT(epfinalend,'%Y年%m月%d') as epfinalend,epcharge,epchargetel,epstate,eplevel,epexplan,eprules,epadwars,epmaxscore,rankingpath,epschool,epstage,epworks,isaccept, eppreexpired, epsecexpired, epfinalexpired, eventgroup, progressStatus, needApprove from events_posted where epschool = #{epschool} and eplevel = #{eplevel} and epstate != 0 ORDER BY epstate, epprestart DESC")
    List<SyseEventsPosted> selectEpschoolAndEplevel(@Param("epschool") String school, @Param("eplevel") String eplevel);

    /*
     * 大赛排名获奖信息（全部）
     * */
    List<Map<String, Object>> RankAll(@Param("epid") Integer epid);
    /*
     * 参赛作品总数（全部）
     * */
    List<SyseEventsPosted> RankAllCount();
    /*
     * 大赛分页（全部)
     * */
    @Select("<script> " +
            "select epid,eptitle,epintro,epsigstart,epfinalend,eplevel,eppic, COUNT(1) zp\n" +
            " from events_posted e, compe_work_scroing_record c \n" +
            " WHERE e.epid=c.compe_id and c.public_status = 1 \n" +
            "<if test=\"epLevel !=null and epLevel != '' \">" +
                "<choose>" +
                    "<when test=\"epLevel == '市区级'\">" +
                        "AND (e.eplevel != '国家级' and e.eplevel != '校园级') " +
                    "</when>" +
                    "<otherwise>" +
                        "AND e.eplevel = #{epLevel} " +
                    "</otherwise>" +
                "</choose>" +
            "</if>" +
            " GROUP BY epid,eptitle,epintro,epsigstart,epfinalend,eplevel,eppic\n" +
            " ORDER BY epid desc " +
            "</script>")
    List<SyseEventsPosted> RankAllPage(@Param("epLevel") String epLevel);
    /**
     * 数据总条数（全部)
     * */
    @Select("select count(1) from events_posted a where EXISTS(select compe_id from compe_work_scroing_record WHERE a.epid=compe_work_scroing_record.compe_id)")
    long RankAllPageCount();


    //根据赛事名称查询
    List<SyseEventsPosted> selectByEptitle(String eptitle);
    /*
     * 查询赛事决赛时间和赛事id、赛事标题
     * */
    @Select("SELECT epid, DATE_FORMAT(epsigstart,'%Y-%m-%d') epsigstart, DATE_FORMAT(epsigend,'%Y-%m-%d') epsigend, \n" +
            "DATE_FORMAT(epprestart,'%Y-%m-%d') epprestart, DATE_FORMAT(eppreend,'%Y-%m-%d') eppreend, \n" +
            "DATE_FORMAT(epsecstart,'%Y-%m-%d') epsecstart, DATE_FORMAT(epsecend,'%Y-%m-%d') epsecend, \n" +
            "DATE_FORMAT(epfinalstart,'%Y-%m-%d') epfinalstart, DATE_FORMAT(epfinalend,'%Y-%m-%d')epfinalend \n" +
            "FROM events_posted\n" +
            "WHERE NOT epstate=2 ")
    List<SyseEventsPosted> selectCompetitionAll();

    /*
     * 根据决赛时间修改状态
     * */
    @Update("update events_posted set epstate=2, progressStatus=2 where epid=#{epid}")
    int editEpstate(int epid);

    /*
     * 修改赛事状态和进度状态
     * */
    @Update("<script> update events_posted set " +
            "<if test=\"epstate !=null and epstate != '' \">" +
            "   epstate=#{epstate}, " +
            "</if>" +
            "progressStatus = #{progressStatus} where epid=#{epid}" +
            "</script>")
    int updateEpstate(SyseEventsPosted eventsPosted);

    //根据赛事Id和赛事阶段查询是那个  具体的赛事
    SyseEventsPosted selectByEpIdAndEpStage(Map<String, Object> param);

    @Select("select * from events_posted where epstate = #{epState} and epschool = #{names}")
    List<SyseEventsPosted> selectSsTeacherSchool(@Param("epState") String epState, @Param("names") String names);

    /**
     * 查询数据是否存在
     * @param syseEventsPosted
     * @return long
     * */
    @Select("<script>" +
            "select count(1) from events_posted where eptitle=#{eptitle} and eplevel=#{eplevel} and epintro=#{epintro} and epstage=#{epstage} and epworks=#{epworks} " +
            "and epsponsor=#{epsponsor} and eporganizer=#{eporganizer} and epsigstart=#{epsigstart} and epsigend=#{epsigend} and epstate=#{epstate} " +
            "and epfinalstart=#{epfinalstart} and epfinalend=#{epfinalend} and epcharge=#{epcharge} and epchargetel=#{epchargetel} and epexplan=#{epexplan} and eprules=#{eprules} and epadwars=#{epadwars} "+
            " <if test='epschool!=\"\" and epschool!=null'>"+
            "   and epschool=#{epschool}"+
            "</if>"+
            " <if test='eppro!=\"\" and eppro!=null'>"+
            "   and eppro=#{eppro}"+
            "</if>"+
            " <if test='epcity!=\"\" and epcity!=null'>"+
            "   and epcity=#{epcity}"+
            "</if>"+
            " <if test='eparea!=\"\" and eparea!=null'>"+
            "   and eparea=#{eparea}"+
            "</if>"+
            " <if test='epprestart!=\"\" and epprestart!=null'>"+
            "   and epprestart=#{epprestart}"+
            "</if>"+
            " <if test='eppreend!=\"\" and eppreend!=null'>"+
            "   and eppreend=#{eppreend}"+
            "</if>"+
            " <if test='epsecstart!=\"\" and epsecstart!=null'>"+
            "   and epsecstart=#{epsecstart}"+
            "</if>"+
            " <if test='epsecend!=\"\" and epsecend!=null'>"+
            "   and epsecend=#{epsecend}"+
            "</if>"+
            "</script>")
    long rechecking(SyseEventsPosted syseEventsPosted);

}
